C:/Projekte/RAIS/common/Query.cs

Go to the documentation of this file.


using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using QueryTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.Query.QueryTypeData>;
using QueryParameters = System.Collections.Generic.Dictionary<string, RAIS.Common.TableManagement.QueryParameter>;

namespace RAIS.Common.TableManagement
{
        public class Query: DBObject
        {
                //*********************************************************************
                #region Enumerators
                public enum CustomizationType
                {
                        ListWithFilter,
                        QueryReport,
                        StatisticsReport,
                        ConsistencyCheck,
                        EntryFilter,
            RanAutogeneration
                }
                public enum QueryType
                {
                        ConsistencyCheck,
                        EntryFilter,
                        PreselectionFilter,
                        PreselectionList,
                        Other,
                        Query,
                        Statistics,
            RanAutogeneration,
            DataRoleRestriction
                }
                #endregion
                //*********************************************************************
                #region Fields
                static QueryTypes queryTypesDictionary;

                QueryType type;
                string queryText;
                bool compiled = false;
                QueryParameters queryParameters = new QueryParameters();
                List<string> newParameterNames = new List<string>();
                #endregion
                //*********************************************************************
                #region Properties
                public static QueryTypes QueryTypesDictionary
                {
                        get { return Query.queryTypesDictionary; }
                        set { Query.queryTypesDictionary = value; }
                }

                public string QueryText
                {
                        get { return queryText; }
            set
            {
                queryText = value;

                List<string> queryVariables = GetVariablesFromQueryText(queryText);
                GetParametersFromQueryText(queryText, queryVariables);
            }
                }
                public bool Compiled
                {
                        get { return this.compiled; }
                        set { this.compiled = value; }
                }
                public QueryType Type
                {
                        get { return type; }
                        set { type = value; }
                }
                public QueryTypeData TypeData
                {
                        get { return queryTypesDictionary[this.TypeText]; }
                }
                public string TypeText
                {
                        get
                        {
                                string result = string.Empty;
                                foreach (KeyValuePair<string, QueryTypeData> queryTypeItem in queryTypesDictionary)
                                        if (queryTypeItem.Value.Type == this.type)
                                        {
                                                result = queryTypeItem.Value.VisibleName;
                                                break;
                                        }
                                return result;
                        }
                        set
                        {
                                this.type = queryTypesDictionary[value].Type;
                        }
                }
                public bool QueryForFilters
                {
                        get
                        {
                                bool queryForFilters = false;
                                if (this.type == QueryType.PreselectionFilter
                                        || this.type == QueryType.PreselectionList
                                        || this.type == QueryType.Query
                                        || this.type == QueryType.Statistics
                                )
                                {
                                        queryForFilters = true;
                                }
                                return queryForFilters;
                        }
                }
                public QueryParameters QueryParameters
                {
                        get { return queryParameters; }
                        set { queryParameters = value; }
                }
                public List<string> NewParameterNames
                {
                        get { return newParameterNames; }
                        set { newParameterNames = value; }
                }
                public string NewParameterNamesInXml
                {
                        get
                        {
                                // Create new QueryParameter to get the ID for default QueryParameterType
                                QueryParameter queryParameter = new QueryParameter();

                                StringBuilder xmlString = new StringBuilder("'<Parameters>");
                                if (this.NewParameterNames.Count != 0)
                                {
                                        int order = 1;
                                        foreach (string parameter in this.NewParameterNames)
                                                xmlString.AppendFormat("<Parameter Name=\"{0}\" TypeID=\"{1}\" Order=\"{2}\" VisibleName=\"{3}\" />"
                                                        , parameter, queryParameter.TypeData.ID, order++, queryParameter.VisibleName);
                                }

                                xmlString.Append("</Parameters>'");

                                return xmlString.ToString();
                        }
                }
                public string QueryNameWithParameters
                {
                        get
                        {
                                StringBuilder queryName = new StringBuilder(this.Name);

                                if (this.type == QueryType.ConsistencyCheck
                                        || this.type == QueryType.EntryFilter
                    || this.type == QueryType.RanAutogeneration)
                                {

                                        queryName.Append("(");
                                        if (this.QueryParameters != null && this.QueryParameters.Count != 0)
                                        {
                                                bool firstParameter = true;
                                                foreach (KeyValuePair<string, QueryParameter> kvp in this.QueryParameters)
                                                {
                                                        QueryParameter queryParameter = kvp.Value;

                                                        if (!firstParameter)
                                                                queryName.Append(", ");

                                                        queryName.Append(queryParameter.VisibleName);
                                                        firstParameter = false;
                                                }
                                        }
                                        queryName.Append(")");
                                }
                                return queryName.ToString();
                        }
                }
                public bool ParametersChanged
                {
                        get
                        {
                                bool changed = false;
                                foreach (string parameterName in this.NewParameterNames)
                                {
                                        if (!this.queryParameters.ContainsKey(parameterName))
                                        {
                                                changed = true;
                                                break;
                                        }
                                }

                                if (!changed)
                                {
                                        foreach (KeyValuePair<string, QueryParameter> kvp in this.QueryParameters)
                                        {
                                                if (!this.NewParameterNames.Contains(kvp.Key))
                                                {
                                                        changed = true;
                                                        break;
                                                }
                                        }
                                }

                                return changed;
                        }
                }
                #endregion
                //*********************************************************************
                #region Constructors
                public Query()
                { }

                public Query(IDataRecord record)
                {
                        this.id = (int)record["PK Query ID"];
                        this.name = record["Query Name"].ToString();
                        this.queryText = record["Query Text"].ToString();
                        this.compiled = ((byte)record["Compiled"] == 0) ? false : true;
                        this.type = (QueryType)Enum.Parse(typeof(QueryType), record["Query Type Name"].ToString());
                }
                #endregion 
                //*********************************************************************
                #region Static Methods
                static public QueryTypeData GetQueryTypeDataByQueryType(QueryType type)
                {
                        QueryTypeData result = null;
                        foreach (KeyValuePair<string, QueryTypeData> queryTypeItem in queryTypesDictionary)
                                if (queryTypeItem.Value.Type == type)
                                {
                                        result = queryTypeItem.Value;
                                        break;
                                }
                        return result;
                }

                static public QueryType GetQueryTypeByCustomizationType(CustomizationType customizationType)
                {
                        QueryType queryType = QueryType.PreselectionList;

                        switch (customizationType)
                        {
                                case Query.CustomizationType.ConsistencyCheck:
                                        queryType = Query.QueryType.ConsistencyCheck;
                                        break;
                                case Query.CustomizationType.EntryFilter:
                                        queryType = Query.QueryType.EntryFilter;
                                        break;
                                case Query.CustomizationType.ListWithFilter:
                                        queryType = Query.QueryType.PreselectionList;
                                        break;
                                case Query.CustomizationType.QueryReport:
                                        queryType = Query.QueryType.Query;
                                        break;
                                case Query.CustomizationType.StatisticsReport:
                                        queryType = Query.QueryType.Statistics;
                                        break;
                                case Query.CustomizationType.RanAutogeneration:
                                        queryType = Query.QueryType.RanAutogeneration;
                                        break;
                        }
                        return queryType;
                }
                #endregion
                //*********************************************************************
                #region Private Methods
                List<string> GetVariablesFromQueryText(string queryText)
                {
                        List<string> queryVariables = new List<string>();

                        string[] linesArray = this.QueryText.Split(new char[] { '\n' });
                        foreach (string line in linesArray)
                        {
                                if (line.Contains("DECLARE"))
                                {
                                        List<string> variables = GetAtMarkedNamesFromString(line);

                                        queryVariables.AddRange(variables);
                                }
                        }
                        return queryVariables;
                }

                void GetParametersFromQueryText(string queryText, List<string> queryVariables)
                {
                        this.newParameterNames.Clear();

                        string[] tempArray = queryText.Split(new string[] { "@@" }, StringSplitOptions.RemoveEmptyEntries);
                        foreach (string text in tempArray)
                        {
                                List<string> atMarkedNames = GetAtMarkedNamesFromString(text);
                                foreach (string atMarkedName in atMarkedNames)
                                {
                                        if (!queryVariables.Contains(atMarkedName)
                                                && !this.newParameterNames.Contains(atMarkedName))
                                        {
                                                this.newParameterNames.Add(atMarkedName);
                                        }
                                }
                        }
                }

                List<string> GetAtMarkedNamesFromString(string text)
                {
                        List<string> atMarkedNames = new List<string>();
                        string[] tempArray = text.Split(new char[] { '@' });
                        if (tempArray.Length > 1)
                        {
                                for (int i = 1; i < tempArray.Length; i++)
                                {
                                        int firstNonLetterOrDigitPosition = 0;
                                        for (int n = 0; n < tempArray[i].Length; n++)
                                        {
                                                if (!Char.IsLetterOrDigit(tempArray[i], n))
                                                {
                                                        firstNonLetterOrDigitPosition = n;
                                                        break;
                                                }
                                                else
                                                {
                                                        if (n == tempArray[i].Length - 1)
                                                                firstNonLetterOrDigitPosition = n + 1;
                                                }
                                        }

                                        if (firstNonLetterOrDigitPosition != 0)
                                        {
                                                string atMarkedName = tempArray[i].Substring(0, firstNonLetterOrDigitPosition);
                                                if (!atMarkedNames.Contains(atMarkedName))
                                                        atMarkedNames.Add(atMarkedName);
                                        }
                                }
                        }
                        return atMarkedNames;
                }
                #endregion
                //*********************************************************************
                #region Nested classes
                public class QueryTypeData : DBTypeObject
                {
                        //*********************************************************************
                        #region Properties
                        public QueryType Type
                        {
                                get { return (QueryType)Enum.Parse(typeof(QueryType), this.name); }
                        }
                        #endregion
                        //*********************************************************************
                        #region Constructors
                        public QueryTypeData()
                        { }

                        public QueryTypeData(IDataRecord record)
                        {
                                this.id = (int)record["PK Query Type ID"];
                                this.name = record["Query Type Name"].ToString();
                                this.visibleName = record["Query Type Visible Name"].ToString();
                        }
                        #endregion
                }
                #endregion
        }
}